import asyncio
from sqlalchemy import text
from src.common.db.database import DatabaseManager
from loguru import logger

class TableInitializer:
    """数据库表初始化器"""
    
    def __init__(self):
        self.db_manager = DatabaseManager()
    
    async def drop_and_recreate_works_table(self):
        """删除并重新创建works表"""
        drop_table_sql = "DROP TABLE IF EXISTS works;"
        
        create_table_sql = """
        CREATE TABLE works (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            platform_type VARCHAR(10) NOT NULL DEFAULT 'douyin',
            platform_name VARCHAR(50) NOT NULL DEFAULT '抖音',
            kol_name VARCHAR(200),
            kol_avatar VARCHAR(500),
            kol_platform_id VARCHAR(50),
            title VARCHAR(500),
            type VARCHAR(255) DEFAULT 'image',
            images TEXT,
            video TEXT,
            cover VARCHAR(500),
            tags VARCHAR(1000),
            content TEXT,
            release_time DATETIME,
            source_link TEXT NOT NULL,
            comment_count BIGINT DEFAULT 0,
            collect_count BIGINT DEFAULT 0,
            like_count BIGINT DEFAULT 0,
            share_count BIGINT DEFAULT 0,
            create_user VARCHAR(100) DEFAULT 'system',
            create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            update_user VARCHAR(100) DEFAULT 'system',
            update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            status INTEGER DEFAULT 1,
            tenant_id VARCHAR(100) DEFAULT 'default'
        );
        """
        
        try:
            async with self.db_manager.get_session() as session:
                # 先删除表
                await session.execute(text(drop_table_sql))
                logger.info("已删除旧的works表")
                
                # 重新创建表
                await session.execute(text(create_table_sql))
                await session.commit()
                logger.info("works表重新创建成功")
                
        except Exception as e:
            logger.error(f"重新创建works表失败: {str(e)}")
            raise
    
    async def create_works_table(self):
        """创建works表（兼容旧方法）"""
        await self.drop_and_recreate_works_table()
    
    async def create_indexes(self):
        """创建索引"""
        indexes = [
            "CREATE INDEX IF NOT EXISTS idx_works_platform_type ON works(platform_type);",
            "CREATE INDEX IF NOT EXISTS idx_works_kol_platform_id ON works(kol_platform_id);",
            "CREATE INDEX IF NOT EXISTS idx_works_release_time ON works(release_time DESC);",
            "CREATE INDEX IF NOT EXISTS idx_works_create_time ON works(create_time DESC);",
            "CREATE INDEX IF NOT EXISTS idx_works_status ON works(status);",
            "CREATE INDEX IF NOT EXISTS idx_works_source_link ON works(source_link);",
            "CREATE INDEX IF NOT EXISTS idx_works_like_count ON works(like_count DESC);",
            "CREATE INDEX IF NOT EXISTS idx_works_type ON works(type);",
            "CREATE INDEX IF NOT EXISTS idx_works_kol_name ON works(kol_name);"
        ]
        
        try:
            async with self.db_manager.get_session() as session:
                for index_sql in indexes:
                    await session.execute(text(index_sql))
                await session.commit()
                logger.info("索引创建成功")
                
        except Exception as e:
            logger.error(f"创建索引失败: {str(e)}")
            raise
    
    async def init_all_tables(self):
        """初始化所有表"""
        try:
            logger.info("开始初始化数据库表...")
            
            # 初始化数据库连接
            await self.db_manager.init_db()
            
            # 创建表
            await self.create_works_table()
            
            # 创建索引
            await self.create_indexes()
            
            logger.info("数据库表初始化完成")
            
        except Exception as e:
            logger.error(f"数据库表初始化失败: {str(e)}")
            raise
        finally:
            await self.db_manager.close_db()
    
    async def check_table_exists(self, table_name: str) -> bool:
        """检查表是否存在"""
        check_sql = """
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=:table_name;
        """
        
        try:
            async with self.db_manager.get_session() as session:
                result = await session.execute(text(check_sql), {"table_name": table_name})
                return result.fetchone() is not None
                
        except Exception as e:
            logger.error(f"检查表存在性失败: {str(e)}")
            return False
    
    async def get_table_info(self, table_name: str):
        """获取表结构信息"""
        info_sql = f"PRAGMA table_info({table_name});"
        
        try:
            async with self.db_manager.get_session() as session:
                result = await session.execute(text(info_sql))
                columns = result.fetchall()
                
                logger.info(f"表 {table_name} 结构信息:")
                for column in columns:
                    logger.info(f"  {column}")
                    
                return columns
                
        except Exception as e:
            logger.error(f"获取表结构信息失败: {str(e)}")
            return []

# 便捷函数
async def init_database():
    """初始化数据库的便捷函数"""
    initializer = TableInitializer()
    await initializer.init_all_tables()

async def check_works_table():
    """检查works表是否存在"""
    initializer = TableInitializer()
    try:
        await initializer.db_manager.init_db()
        exists = await initializer.check_table_exists('works')
        if exists:
            logger.info("works表已存在")
            await initializer.get_table_info('works')
        else:
            logger.warning("works表不存在")
        return exists
    finally:
        await initializer.db_manager.close_db()

if __name__ == "__main__":
    # 直接运行此脚本来初始化数据库
    asyncio.run(init_database())